import logging
from app.models.db import get_db_connection, add_indexes, execute_query
from datetime import datetime

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def init_db():
    """
    初始化数据库，创建所有必要的表，支持PostgreSQL语法和添加索引
    """
    try:
        with get_db_connection() as conn:
            # 1. 管理员表
            create_admin_table(conn)
            
            # 2. 自有库存表
            create_inventory_table(conn)
            
            # 3. 供应商表
            create_supplier_table(conn)
            
            # 4. 供应商虚拟库存表
            create_supplier_inventory_table(conn)
            
            # 5. 出入库单据主表
            create_stock_doc_table(conn)
            
            # 6. 单据明细表
            create_stock_doc_item_table(conn)
            
            # 7. 零库存转移记录表
            create_zero_stock_transfer_table(conn)
            
            # 8. 虚拟库存同步记录表
            create_supplier_inventory_sync_log_table(conn)
            
            # 9. 零库存订单表
            create_zero_stock_order_table(conn)
            
            # 10. 用户表
            create_user_table(conn)
            
            # 插入测试数据
            insert_test_data(conn)
            
            # 添加索引
            add_indexes(conn)
            
            logger.info('数据库初始化完成')
    except Exception as e:
        logger.error(f'数据库初始化失败: {e}')
        raise

def create_admin_table(conn):
    """创建管理员表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS admin (
        id SERIAL PRIMARY KEY,
        username VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('管理员表创建成功')

def create_inventory_table(conn):
    """创建自有库存表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS inventory (
        id SERIAL PRIMARY KEY,
        product_name VARCHAR(255) NOT NULL,
        product_id VARCHAR(100) UNIQUE NOT NULL,
        quantity INTEGER NOT NULL DEFAULT 0,
        warehouse VARCHAR(100) NOT NULL,
        update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('自有库存表创建成功')

def create_supplier_table(conn):
    """创建供应商表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS supplier (
        id SERIAL PRIMARY KEY,
        supplier_name VARCHAR(255) UNIQUE NOT NULL,
        contact VARCHAR(100) NOT NULL,
        phone VARCHAR(50) NOT NULL,
        is_connected INTEGER NOT NULL DEFAULT 1
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('供应商表创建成功')

def create_supplier_inventory_table(conn):
    """创建供应商虚拟库存表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS supplier_inventory (
        id SERIAL PRIMARY KEY,
        supplier_id INTEGER NOT NULL,
        product_id VARCHAR(100) NOT NULL,
        product_name VARCHAR(255) NOT NULL,
        virtual_quantity INTEGER NOT NULL DEFAULT 0,
        threshold INTEGER NOT NULL DEFAULT 10,
        update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (supplier_id) REFERENCES supplier(id)
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('供应商虚拟库存表创建成功')

def create_stock_doc_table(conn):
    """创建出入库单据主表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS stock_doc (
        doc_id VARCHAR(100) PRIMARY KEY,
        doc_type VARCHAR(50) NOT NULL,
        warehouse VARCHAR(100) NOT NULL,
        operator VARCHAR(100) NOT NULL,
        create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        status VARCHAR(50) NOT NULL DEFAULT '已完成'
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('出入库单据主表创建成功')

def create_stock_doc_item_table(conn):
    """创建单据明细表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS stock_doc_item (
        id SERIAL PRIMARY KEY,
        doc_id VARCHAR(100) NOT NULL,
        product_id VARCHAR(100) NOT NULL,
        product_name VARCHAR(255) NOT NULL,
        quantity INTEGER NOT NULL,
        FOREIGN KEY (doc_id) REFERENCES stock_doc(doc_id)
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('单据明细表创建成功')

def create_zero_stock_transfer_table(conn):
    """创建零库存转移记录表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS zero_stock_transfer (
        transfer_id VARCHAR(100) PRIMARY KEY,
        supplier_id INTEGER NOT NULL,
        supplier_name VARCHAR(255) NOT NULL,
        product_id VARCHAR(100) NOT NULL,
        product_name VARCHAR(255) NOT NULL,
        quantity INTEGER NOT NULL,
        direction VARCHAR(50) NOT NULL,
        create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        status VARCHAR(50) NOT NULL DEFAULT '已完成',
        FOREIGN KEY (supplier_id) REFERENCES supplier(id)
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('零库存转移记录表创建成功')

def create_supplier_inventory_sync_log_table(conn):
    """创建虚拟库存同步记录表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS supplier_inventory_sync_log (
        id SERIAL PRIMARY KEY,
        supplier_id INTEGER NOT NULL,
        supplier_name VARCHAR(255) NOT NULL,
        product_id VARCHAR(100) NOT NULL,
        product_name VARCHAR(255) NOT NULL,
        old_quantity INTEGER NOT NULL,
        new_quantity INTEGER NOT NULL,
        operator VARCHAR(100) NOT NULL,
        sync_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (supplier_id) REFERENCES supplier(id)
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('虚拟库存同步记录表创建成功')

def create_zero_stock_order_table(conn):
    """创建零库存订单表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS zero_inventory_orders (
        order_id VARCHAR(100) PRIMARY KEY,
        supplier_id INTEGER NOT NULL,
        supplier_name VARCHAR(255) NOT NULL,
        product_id VARCHAR(100) NOT NULL,
        product_name VARCHAR(255) NOT NULL,
        quantity INTEGER NOT NULL,
        create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        status VARCHAR(50) NOT NULL DEFAULT '已创建',
        operator VARCHAR(100) NOT NULL,
        FOREIGN KEY (supplier_id) REFERENCES supplier(id)
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('零库存订单表(zero_inventory_orders)创建成功')

def create_user_table(conn):
    """创建用户表"""
    sql = '''
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        role VARCHAR(50) NOT NULL DEFAULT 'operator',
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        last_login TIMESTAMP
    )
    '''
    execute_query(conn, sql, commit=True)
    logger.info('用户表创建成功')

def insert_test_data(conn):
    """插入测试数据"""
    # 插入测试供应商
    try:
        # 检查是否已存在数据
        existing = execute_query(conn, "SELECT COUNT(*) FROM supplier")
        if existing and existing[0][0] == 0:
            execute_query(conn, 
                        "INSERT INTO supplier (supplier_name, contact, phone) VALUES (%s, %s, %s)",
                        ('美的核心供应商A', '张三', '13800138000'),
                        commit=True)
            execute_query(conn, 
                        "INSERT INTO supplier (supplier_name, contact, phone) VALUES (%s, %s, %s)",
                        ('美的核心供应商B', '李四', '13900139000'),
                        commit=True)
            logger.info('测试供应商数据插入成功')
    except Exception as e:
        logger.warning(f'插入供应商数据时出错: {e}')
    
    # 插入测试用户
    try:
        # 检查是否已存在admin用户
        existing = execute_query(conn, "SELECT COUNT(*) FROM users WHERE username = 'admin'")
        if existing and existing[0][0] == 0:
            execute_query(conn, 
                        "INSERT INTO users (username, password, role, created_at) VALUES (%s, %s, %s, %s)",
                        ('admin', 'scrypt:32768:8:1$rR6FvtkL4dTSy8ny$c8044d5c3e27cb89f7fd840a5138d3a9d910442f71cc566311e256033767af25990e1cdadc1245b595ac2c77ec1f191019a58980d62aa4b398ac1492c04c3a25', 'admin', datetime.now()),
                        commit=True)
            logger.info('测试用户数据插入成功')
    except Exception as e:
        logger.warning(f'插入用户数据时出错: {e}')

if __name__ == '__main__':
    init_db()